import pymysql
import logging


db_name = 'db_fvacp'
db_user = 'root'
db_pass = '123456'
db_ip = '192.168.0.202'
db_port = 3306


# 写入数据到数据库中
def writeDb(sql, db_data=()):

    """
    连接mysql数据库（写），并进行写的操作
    """
    try:
        conn = pymysql.connect(db=db_name, user=db_user, passwd=db_pass, host=db_ip, port=int(db_port), charset="utf8")
        cursor = conn.cursor()
    except Exception as e:
        print(e)
        logging.error('数据库连接失败:%s' % e)
        return False

    try:
        cursor.execute(sql, db_data)
        id_last = str(cursor.lastrowid)
        # print('xx'+str(cursor.lastrowid))
        conn.commit()
    except Exception as e:
        conn.rollback()
        logging.error('数据写入失败:%s' % e)
        return False
    finally:
        cursor.close()
        conn.close()
    return True, id_last


def writeDb1(sql, db_data=()):

    """
    连接mysql数据库（写），并进行写的操作
    """
    try:
        conn = pymysql.connect(db=db_name, user=db_user, passwd=db_pass, host=db_ip, port=int(db_port), charset="utf8")
        cursor = conn.cursor()
    except Exception as e:
        print(e)
        logging.error('数据库连接失败:%s' % e)
        return False

    try:
        cursor.execute(sql, db_data)
        # id_last = str(cursor.lastrowid)
        # print('xx'+str(cursor.lastrowid))
        conn.commit()
    except Exception as e:
        conn.rollback()
        logging.error('数据写入失败:%s' % e)
        return False
    finally:
        cursor.close()
        conn.close()
    return True


def Tuple_A(data):  # 10进制元组转16进制字符串并拼接
    datalist = ''
    lin = ['%02X' % i for i in data]
    for i in range(len(lin)):
        datalist += str(lin[i]) + ' '  # 指静脉模板
    return datalist


def Tuple_B(data):
    datalist = ''
    lin = ['%02X' % i for i in data]
    for i in range(len(lin)):
        datalist += str(lin[i])  # 普通数据
    return datalist


def Trans_endian(data):  # 转换大小端
    datalist = ''
    lin = ['%02X' % i for i in data]
    lin_tran = lin[::-1]  # 序列逆序
    for i in range(len(lin_tran)):
        datalist += str(lin_tran[i])
    datalist_int = eval('0x' + datalist)
    return datalist_int


def Delete_sql():
    sql_del = '''
    DELETE FROM bus_personnel
    WHERE
        personnelcode IN (
            SELECT t.personnelcode FROM
                (
                    SELECT
                        personnelcode
                    FROM
                        bus_personnel
                    GROUP BY
                        personnelcode
                    HAVING
                        count(1) > 1
                ) t
        )
AND id NOT IN (
SELECT dt.id FROM
    (
        SELECT
            min(id) AS id
        FROM
            bus_personnel
        GROUP BY
            personnelcode
        HAVING
            count(1) > 1
    ) dt
)
                '''

    """
    连接mysql数据库（写），并进行的操作
    """
    try:
        conn = pymysql.connect(db=db_name, user=db_user, passwd=db_pass, host=db_ip, port=int(db_port), charset="utf8")
        cursor = conn.cursor()
    except Exception as e:
        print(e)
        logging.error('数据库连接失败:%s' % e)
        return False

    try:
        cursor.execute(sql_del)
        conn.commit()
    except Exception as e:
        conn.rollback()
        logging.error('数据写入失败:%s' % e)
        return False
    finally:
        cursor.close()
        conn.close()
    return True

